package com.crm.util;

import java.io.File;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.crm.dao.InventoryDAO;
import com.crm.po.Inventory;
import com.crm.vo.Excel;

public class ExcelEx {

	private static Logger logger = Logger.getLogger(ExcelEx.class);
	
	private static SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd");
	
	public static List<Inventory> getInventorys__(){
		StringBuffer Hql = new StringBuffer(" from Inventory t where 1=1 ");
		int page = 1;//当前第几页
		int rows = 10000;//每页显示几行

		InventoryDAO dao = new InventoryDAO();
		Hql.append(" order by t.sort ");
	    List<Inventory> list = dao.inventoryListPage(Hql.toString(), (page-1) * rows, rows);
	    
	    return list;
	} 
	
	public static void toExcel(HttpServletRequest request, HttpServletResponse response, Excel excel) {
		try {
			
			String excelName = excel.getFileName();
			String[][] grids = excel.getGrids();
			
			OutputStream os = response.getOutputStream();// 取得输出流   
            response.reset();// 清空输出流  

            response.setHeader("Content-disposition", "attachment; filename=" + new String(excelName.getBytes("GBK"),"ISO-8859-1") + ".xls");// 设定输出文件头   
            response.setContentType("application/msexcel");// 定义输出类型 
            
            WritableWorkbook book = Workbook.createWorkbook(os); // 建立excel文件   
			// 打开文件
			//WritableWorkbook book = Workbook.createWorkbook(new File("D://test.xls"));
			// 生成名为“第一页”的工作表，参数0表示这是第一页
			WritableSheet sheet = book.createSheet(excelName, 0);
			// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
			// 以及单元格内容为test
			Label label = null;
			for(int i = 0; i < grids.length; i ++){
				for(int j = 0; j < grids[i].length; j ++){
					label = new Label(j, i, grids[i][j]);
					sheet.addCell(label);
				}
			}

			book.write();
			book.close();
			
			os.close();
			response.flushBuffer();

		} catch (Exception e) {
			logger.error("export data to Excel fail:", e);
		}
	}
	
	public static String[][] readExcel(File file, int sheetNumber, int rowNumber, int colNumber) {
		String[][] grids = new String[rowNumber][colNumber];
		Workbook book = null;
		try {
			book = Workbook.getWorkbook(file);
            Sheet sheet = book.getSheet(sheetNumber);
            for(int i = 0; i < rowNumber; i ++){
            	for(int j = 0; j < colNumber; j ++){
            		Cell cell = sheet.getCell(j, i);
            		String content = "";
            		if(cell.getType() == CellType.DATE){
            			DateCell dateCell = (DateCell) cell;
            			content = formatDate.format(dateCell.getDate());
            		}else{
            			content = cell.getContents();
            		}
            		grids[i][j] = content;
                }
            }
		} catch (Exception e) {
			logger.error("export data to Excel fail:", e);
		} finally {
			if(book != null){
				book.close();
			}
		}
		return grids;
	}

}